<?php
/***************************************************************************
 *                             pilot_no_fly.php
 *                            -------------------
 *                       (APU) Automated Pilot Utility
 *   begin                : Sunday, Dec 25, 2005
 *   copyright            : (C) 2005 SF Games
 *   email                : techsupport@sf-games.com
 *
 *   This script will mark all pilots who have not flown in the last n days
 *   as inactive, where n is a number of days set in the config.inc file.
 *   n must be a number greater than or equal to 30
 *   any number less than 30 will be considered a never expire pilot value.
 ***************************************************************************/

	$apus_root_path = '../include/';
	include($apus_root_path . 'common.php');
	include($apus_root_path . 'config.php');

	// Uses $noflyDaysInactive and $noFlyDaysDelete from config.inc

	//
	// Begin HTML
	//

	echo "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">\n";
	echo "<html>\n";
	echo "<head>\n";
	echo "<title>APU Pilot Administration</title>\n";
	echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">\n";
	echo "</head>\n";
	echo "<body>\n";
	echo "<div align=\"center\">\n";
	echo("<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"4\" color=\"#990000\"><b>(APU) Automated Pilot Utility<br>Grooming No Fly Pilots</b></font><br><br>\n");

	// Begin a table to show Pilot status
	echo "<table width=\"300\" border=\"1\" cellspacing=\"2\" cellpadding=\"1\" align=\"center\">\n";

	// Begin table column names
	echo "<tr>\n";
	echo "<td width=\"75\" bgcolor=#ffffff><font color=\"#000000\" size=\"2\"><b>Pilots Name</b></font></td>\n";
	echo "<td width=\"75\"  bgcolor=#ffffff><font color=\"#000000\" size=\"2\"><b>ID Number</b></font></td>\n";
	echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\"><b>Inactive</b></font></div></td>\n";
	echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\"><b>Deleted</b></font></div></td>\n";
	echo "</tr>\n";

	//
	// Get today
	//

	$today   = getdate();
	$endDate = mktime(12, 1, 0, $today["mon"], $today["mday"], $today["year"]);

	//
	// Attempt connection to mySQL
	//

	$db = mysql_connect($dbhost, $dbuname, $dbpass)
		or die("Could not connect to the SQL server<br>");
	mysql_select_db($dbname);

	//
	// Query active non deleted pilots
	//

	$query  = "SELECT * FROM " .$table_prefix ."PILOTS WHERE ACTIVE = '1' AND LOA = '0' AND DELETED = '0' ORDER BY PILOT_ID";
	$pilots = mysql_query($query);
	if ( !$pilots )
		die("Query for active pilots failed.<br>Click your browsers Refresh button to try again.<br>If you continue to receive this error, contact the webmaster<br>Failed with mySQL error: " . mysql_error());

	//
	// For each pilot, check number of days for inactive and deletion marking
	// Report each pilot on this page
	//

	while ($row = mysql_fetch_array($pilots, MYSQL_ASSOC))
	{
		// Get this pilots last fly date, format is mySQL date format as string YYYY-MM-DD
		$lastFlyDate  = $row["LAST_FLY"];

		// Parse for start date - Is in format YYYY-MM-DD
		$startYear    = intval(substr($lastFlyDate, 0, 4));
		$startMonth   = intval(substr($lastFlyDate, 5, 2));
		$startDay     = intval(substr($lastFlyDate, 8, 2));
		$startDate    = mktime(12, 1, 0, $startMonth, $startDay, $startYear);
		$diffArray    = date_diff($startDate, $endDate);

		// Display Pilot Status
		echo "<tr>\n";
		echo "<td width=\"105\" bgcolor=#ffffff><font color=\"#000000\" size=\"2\">" .$row["FIRST_NAME"] ." " ,$row["LAST_NAME"] ."</font></td>\n";
		echo "<td width=\"75\"  bgcolor=#ffffff><font color=\"#000000\" size=\"2\">" .$row["PILOT_ID"] ."</font></td>\n";

		// Mark this pilot as deleted if no flight in N days. Example 60 days
		if ( $diffArray['days'] > $noFlyDaysDelete )
		{
			$query = "UPDATE " .$table_prefix ."PILOTS SET ACTIVE='0', DELETED='1' WHERE PILOT_ID = " .sqlfix($row["PILOT_ID"]);
			$result = mysql_query($query);
			if ( !$result )
				die("Updating of PILOTS record failed.<br>Click your browsers back button to try again.<br>Failed with mySQL error: " . mysql_error());

			echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\">Yes</font></div></td>\n";
			echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\">Yes</font></div></td>\n";

			// Send an E-mail
			$to       = $row["EMAIL"];
			$to      .= ", ";
			$to      .= $adminEmailAddress;
			$subject  = "Deleted Pilot Notification";
			$message  = $row["FIRST_NAME"] ." " .$row["LAST_NAME"] ." - " .$row["PILOT_ID"] ."<br>\n";
			$message .= "Warning. You have not submitted a PIREP in the last " .$diffArray['days'] ." days.<br>\n";
			$message .= "Your last PIREP was submitted on " .$row["LAST_FLY"] ."<br><br>\n";
			$message .= "The Automated Pilot Utility (APU), has flagged you as Deleted, and you will not appear on the HUB Roster.<br>\n";
			$message .= "Contact your HUB Captain to get you off of the Deleted list, and get some flights in to stay current.<br>\n";
			$headers  = "MIME-Version: 1.0" .getEmailCRLF($bSpecialCRLF);
			$headers  = "Content-type: text/html; charset=iso-8859-1" .getEmailCRLF($bSpecialCRLF);
			$headers .= "From: " .$emailFromFriendly ."<" .$emailFromAddress .">" .getEmailCRLF($bSpecialCRLF);
			mail($to, $subject, $message, $headers);
		}
		// Mark this pilot as inactive if no flight in N days. Example 30 days
		else if ( $diffArray['days'] > $noflyDaysInactive )
		{
			$query = "UPDATE " .$table_prefix ."PILOTS SET ACTIVE='0' WHERE PILOT_ID = " .sqlfix($row["PILOT_ID"]);
			$result = mysql_query($query);
			if ( !$result )
				die("Updating of PILOTS record failed.<br>Click your browsers back button to try again.<br>Failed with mySQL error: " . mysql_error());

			echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\">Yes</font></div></td>\n";
			echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\">No</font></div></td>\n";

			// Send an E-mail
			$to       = $row["EMAIL"];
			$to      .= ", ";
			$to      .= $adminEmailAddress;
			$subject  = "Inactive Pilot Notification";
			$message  = $row["FIRST_NAME"] ." " .$row["LAST_NAME"] ." - " .$row["PILOT_ID"] ."<br>\n";
			$message .= "Warning. You have not submitted a PIREP in the last " .$diffArray['days'] ." days.<br>\n";
			$message .= "Your last PIREP was submitted on " .$row["LAST_FLY"] ."<br><br>\n";
			$message .= "The Automated Pilot Utility (APU), has flagged you as Inactive.<br>\n";
			$message .= "Contact your HUB Captain to get you off of the Inactive list, and get some flights in to stay current.<br>\n";
			$headers  = "MIME-Version: 1.0" .getEmailCRLF($bSpecialCRLF);
			$headers  = "Content-type: text/html; charset=iso-8859-1" .getEmailCRLF($bSpecialCRLF);
			$headers .= "From: " .$emailFromFriendly ."<" .$emailFromAddress .">" .getEmailCRLF($bSpecialCRLF);
			mail($to, $subject, $message, $headers);
		}
		else
		{
			echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\">No</font></div></td>\n";
			echo "<td width=\"75\"  bgcolor=#ffffff><div align=\"center\"><font color=\"#000000\" size=\"2\">No</font></div></td>\n";
		}

		// End the row
		echo "</tr>\n";
	}

	// End the table
	echo "</table>\n";
	echo "<br>\n";

	// HTML footer
	echo("<div align=\"center\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"#990000\">" .$APU_COPYRIGHT ."</font></div><br>\n");
	echo "</div>\n";
	echo "</body>\n";
	echo "</html>\n";
?>
